﻿using MiniFox;
using NPOI.SS.UserModel;
using System.Text.RegularExpressions;

namespace DataTransfer.Core
{
    /// <summary>
    /// 
    /// </summary>
    public static class ExcelTransferUtility
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="dataValue"></param>
        /// <param name="type"></param>
        public static void SetCellValue(ICell cell, object dataValue, Type type)
        {
            switch (Type.GetTypeCode(type))
            {
                case TypeCode.Boolean:
                    cell.SetCellType(CellType.Boolean);
                    cell.SetCellValue(Convert.ToBoolean(dataValue));
                    break;
                case TypeCode.Single:
                case TypeCode.Double:
                case TypeCode.Decimal:
                    cell.SetCellType(CellType.Numeric);
                    cell.SetCellValue(Convert.ToDouble(dataValue));
                    break;
                case TypeCode.Byte:
                    cell.SetCellType(CellType.Numeric);
                    cell.SetCellValue(Convert.ToByte(dataValue));
                    break;
                case TypeCode.SByte:
                    cell.SetCellType(CellType.Numeric);
                    cell.SetCellValue(Convert.ToSByte(dataValue));
                    break;
                case TypeCode.UInt16:
                    cell.SetCellType(CellType.Numeric);
                    cell.SetCellValue(Convert.ToUInt16(dataValue));
                    break;
                case TypeCode.UInt32:
                    cell.SetCellType(CellType.Numeric);
                    cell.SetCellValue(Convert.ToUInt32(dataValue));
                    break;
                case TypeCode.UInt64:
                    cell.SetCellType(CellType.Numeric);
                    cell.SetCellValue(Convert.ToUInt64(dataValue));
                    break;
                case TypeCode.Int16:
                    cell.SetCellType(CellType.Numeric);
                    cell.SetCellValue(Convert.ToInt16(dataValue));
                    break;
                case TypeCode.Int32:
                    cell.SetCellType(CellType.Numeric);
                    cell.SetCellValue(Convert.ToInt32(dataValue));
                    break;
                case TypeCode.Int64:
                    cell.SetCellType(CellType.Numeric);
                    cell.SetCellValue(Convert.ToInt64(dataValue));
                    break;
                case TypeCode.DateTime:
                    if (dataValue != null)
                    {
                        cell.SetCellType(CellType.String);
                        cell.SetCellValue(String.Format("{0:" + GlobalStandardFormat.SIMPLE_DATETIME_FORMAT + "}", dataValue));
                    }
                    else
                    {
                        cell.SetCellType(CellType.Blank);
                        cell.SetCellValue(string.Empty);
                    }
                    break;
                case TypeCode.Char:
                case TypeCode.String:
                    cell.SetCellType(CellType.String);
                    cell.SetCellValue(Convert.ToString(dataValue));
                    break;
                case TypeCode.Empty:
                case TypeCode.DBNull:
                    cell.SetCellType(CellType.Blank);
                    cell.SetCellValue(string.Empty);
                    break;

                case TypeCode.Object:
                    if (dataValue != null)
                    {
                        cell.SetCellType(CellType.String);
                        if (type == typeof(DateTimeOffset))
                            cell.SetCellValue(String.Format("{0:" + GlobalStandardFormat.DATETIME_OFFSET_FORMAT + "}", dataValue));
                        else
                            cell.SetCellValue(Convert.ToString(dataValue));
                    }
                    else
                    {
                        cell.SetCellType(CellType.Blank);
                        cell.SetCellValue(string.Empty);
                    }
                    break;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        /// <exception cref="MiniFox.Exceptions.SystemException"></exception>
        public static object GetCellValue(ICell cell)
        {
            object val = null;
            if (cell != null)
            {
                switch (cell.CellType)
                {
                    case CellType.Boolean:
                        val = cell.BooleanCellValue;
                        break;
                    case CellType.Numeric:

                        if (DateUtil.IsCellDateFormatted(cell))//日期类型
                        {
                            val = cell.DateCellValue;
                        }
                        else//其他数字类型
                        {
                            val = cell.NumericCellValue;
                        }
                        break;
                    case CellType.Blank:
                        val = null;
                        break;
                    case CellType.Formula:
                        throw new MiniFox.Exceptions.SystemException($"上传文件中{ToName(cell.ColumnIndex) + (cell.RowIndex + 1)}单元格包含公式无法导入，请检查数据");
                    case CellType.Error:
                        throw new MiniFox.Exceptions.SystemException("请检查上传文件中是否包含错误单元格（如：#N/A）");
                    default:
                        val = cell.StringCellValue;
                        break;
                }
            }
            return val;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static string ToName(int index)
        {
            if (index < 0) { throw new Exception("invalid parameter"); }

            List<string> chars = new List<string>();
            do
            {
                if (chars.Count > 0) index--;
                chars.Insert(0, ((char)(index % 26 + (int)'A')).ToString());
                index = (int)((index - index % 26) / 26);
            } while (index > 0);

            return string.Join(string.Empty, chars.ToArray());
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="value"></param>
        /// <param name="expression"></param>
        /// <returns></returns>
        public static bool CheckRegString(string value, string expression)
        {
            Regex Reg = new Regex(expression);
            return Reg.IsMatch(value);
        }

        ///// <summary>
        ///// Cell单元格的值转化为String类型
        ///// </summary>
        ///// <param name="cell"></param>
        ///// <returns></returns>
        //public static string ToStringValue(this ICell cell)
        //{
        //    object val = null;
        //    if (cell != null)
        //    {
        //        switch (cell.CellType)
        //        {
        //            case CellType.Boolean:
        //                val = cell.BooleanCellValue;
        //                break;
        //            case CellType.Numeric:

        //                if (DateUtil.IsCellDateFormatted(cell))//日期类型
        //                {
        //                    val = cell.DateCellValue;
        //                }
        //                else//其他数字类型
        //                {
        //                    val = cell.NumericCellValue;
        //                }
        //                //val = cell.NumericCellValue;
        //                break;
        //            case CellType.Blank:
        //                val = null;
        //                break;
        //            case CellType.Formula:
        //                throw new MiniFox.Exceptions.SystemException("请检查上传文件中是否包含公式");
        //            case CellType.Error:
        //                throw new MiniFox.Exceptions.SystemException("请检查上传文件中是否包含错误单元格（如：#N/A）");
        //            default:
        //                val = cell.StringCellValue.Replace("\n", "").Replace("\r", "").Trim();
        //                break;
        //        }
        //    }
        //    if (val != null)
        //        return val.ToString();
        //    else
        //        return null;
        //}
        ///// <summary>
        ///// 验证Excel数据类型
        ///// </summary>
        ///// <param name="dataType">数据类型</param>
        ///// <param name="cell">Cell</param>
        ///// <returns>布尔(true成功false失败)</returns>
        //internal static bool ValidateType(DataType? dataType, ICell cell)
        //{
        //    bool falg = false;
        //    switch (dataType)
        //    {
        //        case DataType.DateTime:
        //            falg = DateTime.TryParse(cell.ToStringValue(), out DateTime date);
        //            break;
        //        case DataType.Boolean:
        //            falg = Boolean.TryParse(cell.ToStringValue(), out bool booler);
        //            break;
        //        case DataType.Number:
        //            falg = float.TryParse(cell.ToStringValue(), out float num);
        //            break;
        //        case DataType.String:
        //        case DataType.WorkID:
        //        case DataType.Name:
        //            falg = true;
        //            break;
        //        case DataType.Time:
        //            falg = DateTime.TryParse(cell.ToStringValue(), out DateTime datetime);
        //            if (falg)
        //                falg = TimeSpan.TryParse(datetime.ToString("HH:mm:ss"), out TimeSpan timeSpan);
        //            break;
        //        case DataType.Email:
        //            falg = CheckRegString(cell.ToStringValue(), @"^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$");
        //            break;
        //        case DataType.Mobile:
        //            falg = CheckRegString(cell.ToStringValue(), @"^(13[0-9]|14[5|7]|15[0|1|2|3|5|6|7|8|9]|18[0|1|2|3|5|6|7|8|9])\d{8}$");
        //            break;
        //        case DataType.Phone:
        //            falg = CheckRegString(cell.ToStringValue(), @"\d{3}-\d{8}|\d{4}-\d{7}");
        //            break;
        //        case DataType.Sex:
        //            if (cell.ToStringValue() == "男" || cell.ToStringValue() == "女")
        //                falg = true;
        //            break;
        //        default:
        //            break;
        //    }
        //    return falg;
        //}

        //public static IWorkbook ExportToExcel(this DataSet dataSet)
        //{
        //    IDataExporter exporter = ComponentFactory.GetComponent<IDataExporter>();
        //    IWorkbook workbook = exporter.ExportDataToExcel(dataSet);

        //    return workbook;
        //}
        //public static IWorkbook ExportToExcel(this DataTable dataTable)
        //{
        //    IDataExporter exporter = ComponentFactory.GetComponent<IDataExporter>();
        //    IWorkbook workbook = exporter.ExportDataToExcel(dataTable);

        //    return workbook;
        //}


        //public static DataTable ImportToDataTable(this IWorkbook workbook)
        //{
        //    IDataImporter importer = ComponentFactory.GetComponent<IDataImporter>();
        //    DataTable dataTable = importer.ImportExcelToDataTable(workbook);

        //    return dataTable;
        //}

        ///// <summary>
        ///// 判断Excel Cell是否有相同数据
        ///// </summary>
        ///// <param name="arr"></param>
        ///// <returns></returns>
        //public static string IsCellCompareName(List<ICell> Cells)
        //{
        //    var list = Cells.OrderByDescending(a => a.ToStringValue()).ToList();

        //    // 对比相邻的数据是否相同
        //    for (var i = 0; i < list.Count - 1; i++)
        //    {
        //        if (list[i].ToStringValue() == list[i + 1].ToStringValue())
        //            return list[i].ToStringValue();
        //    }
        //    return string.Empty;
        //}
    }
}
